package com.hutao.manage.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelUtil {

	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
	public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
	
	
	public static void main(String[] args) throws IOException {
		readExecl("D://5.xls");
	}

	/**
	 * 
	 * <p>
	 * Title: readExecl
	 * </p>
	 * <p>
	 * Description:读取Execl
	 * </p>
	 * 
	 * @author zhouaokai
	 */
	public static List<Map<String,Object>> readExecl(String path) throws IOException {
		File file = new File(path);
		List<Map<String,Object>> list = null;
		if (file.exists()) {
			String[] split = path.split("\\.");
			if (OFFICE_EXCEL_2003_POSTFIX.equals(split[split.length - 1])) {
				list = readXls(path);
			} else if (OFFICE_EXCEL_2010_POSTFIX.equals(split[split.length - 1])) {
				list = readXlsx(path);
			}
		}
		return list;
	}

	/**
	 * 
	 * <p>
	 * Title: readExecl
	 * </p>
	 * <p>
	 * Description:读取Execl
	 * </p>
	 * 
	 * @author zhouaokai
	 */
	public static List<Map<String,Object>> readXlsx(String path) throws IOException {
		FileInputStream is = new FileInputStream(path);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		int numberOfSheets = xssfWorkbook.getNumberOfSheets();
		XSSFRow xssfRow = null;
		
		List<Map<String,Object>> mapList = new ArrayList<>();
		Map<String,Object> map = null;
		try {
			for (int s = 0; s < numberOfSheets; s++) {
				try {
					XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
					for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
						map = new LinkedHashMap<>();
						xssfRow = xssfSheet.getRow(rowNum);
						if (xssfRow != null) {
							for (int i = 0; i < xssfRow.getLastCellNum(); i++) {
								XSSFCell cell = xssfRow.getCell(i);
								if (cell == null) {
									map.put(i+"", null);
								}else {
									String value = getValue(cell);
									map.put(i+"", value);
								}
								
								
								
								
							}
						}
						
						mapList.add(map);
						
						System.out.println(map);
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} finally {
			is.close();
		}
		return mapList;
	}

	/**
	 * 
	 * <p>
	 * Title: readExecl
	 * </p>
	 * <p>
	 * Description:读取Execl
	 * </p>
	 * 
	 * @author zhouaokai
	 */
	public static List<Map<String,Object>> readXls(String path) throws IOException {
		List<Map<String,Object>> mapList = new ArrayList<>();
		FileInputStream is = new FileInputStream(path);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		int numberOfSheets = hssfWorkbook.getNumberOfSheets();
		Map<String,Object> map = null;
		try {
			for (int s = 0; s < numberOfSheets; s++) {
				try {
					HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(s);
					
					HSSFRow hssfRow = hssfSheet.getRow(0);
					for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
						hssfRow = hssfSheet.getRow(rowNum);
						map = new LinkedHashMap<>();
						if (hssfRow != null) {
							for (int i = 0; i < hssfRow.getLastCellNum(); i++) {
								HSSFCell cell = hssfRow.getCell(i);
								if (cell == null) {
									map.put(i+"", null);
								}else {
									String value = getValue(cell);
									map.put(i+"", value);
								}
								
								
							}
						}
						System.out.println(map);
						mapList.add(map);
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} finally {
			is.close();
		}
		return mapList;
	}

	@SuppressWarnings({ "static-access" })
	private static String getValue(XSSFCell xssfRow) {
		// 将数字当成字符串来读，防止将1读取成1.0
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			xssfRow.setCellType(xssfRow.CELL_TYPE_STRING);
		}
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfRow.getNumericCellValue());
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}

	@SuppressWarnings({ "static-access" })
	private static String getValue(HSSFCell hssfCell) {
		// 将数字当成字符串来读，防止将1读取成1.0
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			hssfCell.setCellType(hssfCell.CELL_TYPE_STRING);
		}
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

}
